/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.uca.sisbec.helpers.becario;

import com.uca.sisbec.beans.CodeAndName;
import com.uca.sisbec.commons.HibernateAction;
import com.uca.sisbec.commons.Utils;
import com.uca.sisbec.forms.becario.AsistenciaBecariosForm;
import com.uca.sisbec.forms.becario.AsistenciaReunionForm;
import com.uca.sisbec.forms.becario.MotivoReunionForm;
import com.uca.sisbec.mapping.Reunion;
import com.uca.sisbec.mapping.Becario;
import com.uca.sisbec.mapping.BecarioXReunion;
import com.uca.sisbec.mapping.BecarioXReunionId;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.Query;

/**
 *
 * @author Jorgore
 */
public class AsistenciaBecariosHelper extends HibernateAction {

    private Session session;

    public AsistenciaBecariosHelper(Session session) {
        this.session = session;
    }

    public List<Reunion> getReunionesPasadas() {
        String hql = "select new com.uca.sisbec.beans.CodeAndName(idReunion, tema) from Reunion "
                + "where horafechaReunion <= :v_now "
                + "order by horafechaReunion desc";
        List<Reunion> listReu;
        Query query = session.createQuery(hql);
        query.setDate("v_now", new Date());
        listReu = new ArrayList<Reunion>(query.list());
        return listReu;
    }

    public List<BecarioXReunion> ListaNOAsistenciaBD(String idReunion) {
        List<BecarioXReunion> listBxr = null;
        Query query = session.createQuery("FROM BecarioXReunion bxr where bxr.id.idReunion = " + idReunion);
        listBxr = query.list();
        return listBxr;
    }

    public List<Becario> ListaBecarioDB(String hql) {
        List<Becario> listBecario = null;
        Query query = session.createQuery(hql/*
                 * "FROM Becario b"
                 */);
        listBecario = query.list();
        return listBecario;
    }

    public void InsertarInasistenciaBD(AsistenciaBecariosForm abf) throws SQLException, HibernateException, ParseException {
        Reunion r = (Reunion) session.get(Reunion.class, abf.getIdReunion());
        Becario b = (Becario) session.get(Becario.class, abf.getCarnet());
        BecarioXReunionId id = new BecarioXReunionId();
        BecarioXReunion bxr = new BecarioXReunion();


        id.setCarnet(abf.getCarnet());
        id.setIdReunion(abf.getIdReunion());
        bxr.setId(id);
        bxr.setMotivoFalta(abf.getMotivoFalta());
        bxr.setBecario(b);
        bxr.setReunion(r);
        session.save(bxr);

    }

    public void eliminarRegistroBD(String idReunion, String carnet) {
        BecarioXReunionId id = new BecarioXReunionId();
        id.setCarnet(carnet);
        id.setIdReunion(Integer.parseInt(idReunion));
        BecarioXReunion bxr = (BecarioXReunion) session.get(BecarioXReunion.class, id);
        session.delete(bxr);
    }

    public void modificarRegistroBD(AsistenciaBecariosForm abf, String idReunion) {
        BecarioXReunionId id = new BecarioXReunionId();
        id.setIdReunion(Integer.parseInt(idReunion));
        id.setCarnet(abf.getCarnet());
        BecarioXReunion bxr = (BecarioXReunion) session.get(BecarioXReunion.class, id);

        bxr.setMotivoFalta(abf.getMotivoFalta());
        session.update(bxr);
    }

    public boolean InasistenciaEnBD(String idReunion, AsistenciaBecariosForm abf) {
        String hql = "From BecarioXReunion where id.idReunion = " + Integer.parseInt(idReunion)
                + "and id.carnet = '" + abf.getCarnet() + "'";
        Query query = session.createQuery(hql);
        //query.setInteger("v_idReunion",Integer.parseInt(idReunion));
        //query.setString("v_carnet", abf.getCarnet());
        BecarioXReunion bxr = (BecarioXReunion) query.uniqueResult();
        return (bxr != null);
    }

    public Reunion getReunionById(int reunion) {
        return (Reunion) session.get(Reunion.class, reunion);
    }

    public List<MotivoReunionForm> getBecariosXReunion(int reunion) {
        String hql = "select new com.uca.sisbec.forms.becario.MotivoReunionForm"
                + "(b.carnet, concat(b.apellidos, ', ', b.nombres), br.motivoFalta) "
                + "from Becario b, BecarioXReunion br "
                + "where br.id.idReunion = :v_idReu "
                + "and b.carnet = br.id.carnet ";
        Query query = session.createQuery(hql);
        query.setInteger("v_idReu", reunion);
        return new ArrayList<MotivoReunionForm>(query.list());
    }

    public void setBecariosXReunionDefault(int reunion) {
        String hql = "select carnet "
                + "from Becario "
                + "where estado = 'A' ";
        Query query = session.createQuery(hql);
        List<String> becarios = new ArrayList<String>(query.list());
        BecarioXReunion bxr = null;
        BecarioXReunionId id = null;
        
        for (int i = 0; i < becarios.size(); i++){
            bxr = new BecarioXReunion();
            id = new BecarioXReunionId(reunion, becarios.get(i));
            bxr.setId(id);
            bxr.setMotivoFalta("AS");
            session.save(bxr);
        }
    }
    
    public int eliminarAsistencia(int reunion){
        String hql = "delete from BecarioXReunion where id.idReunion = :v_idReu ";
        Query query = session.createQuery(hql);
        query.setInteger("v_idReu", reunion);
        int rows = query.executeUpdate();
        return rows;
    }

    public void guardarAsistencia(AsistenciaReunionForm arf) {
        List<MotivoReunionForm> becarios = new ArrayList<MotivoReunionForm>(arf.getMotivo().getItemsToShow());
        BecarioXReunion bxr = null;
        BecarioXReunionId id = null;
        int reunion = arf.getReunion();
        
        for (int i = 0; i < becarios.size(); i++){
            bxr = new BecarioXReunion();
            id = new BecarioXReunionId(reunion, becarios.get(i).getCarnet());
            bxr.setId(id);
            bxr.setMotivoFalta(becarios.get(i).getMotivo());
            session.save(bxr);
        }
    }
}
